﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using Northwind.NET.Settings;
using Northwind.NET.Model;
using System.Data.Common;
using System.Collections;
using Alachisoft.NosDB.Common;

namespace Northwind.NET.EDM.FE.WinForms.Controls
{
    public partial class OrdersUserControl : BaseUserControl
    {
        private IList<Customer> _customers = new List<Customer>();
        private IList<Employee> _employees = new List<Employee>();
        private IList<Shipper> _shippers = new List<Shipper>();

        private string CurrentOrderID { get; set; }
        private string CurrentEmployeeID { get; set; }
        private string CurrentShipperID { get; set; }

        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);

            new System.Threading.Tasks.TaskFactory().StartNew(() =>
            {
                LoadAllCustomers();
                LoadAllEmployees();
                LoadAllShippers();
            });

            LoadOrders();
            LoadTheEmployee();
            string customerId = setOrderDetailsDataSource("10248");// Loading the first order.
            LoadACustomers(customerId);
            LoadShippers();
            setBillToValues(customerId);
        }

        private void LoadAllCustomers()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT ID, Code, Name, ContactName, ContactTitle, Address, City, Region, " + 
                                      "PostalCode, Country, Phone, Fax FROM Customers";

                IDataReader customers = command.ExecuteReader();
                while(customers.Read())
                {
                    Customer newCustomer = new Customer();
                    newCustomer.ID = customers.GetValue(0) == null ? "" : customers.GetInt32(0).ToString();
                    newCustomer.Code = customers.GetValue(1) == null ? "" : customers.GetString(1).ToString();
                    newCustomer.Name = customers.GetValue(2) == null ? "" : customers.GetString(2).ToString();
                    newCustomer.ContactName = customers.GetValue(3) == null ? "" : customers.GetString(3).ToString();
                    newCustomer.ContactTitle = customers.GetValue(4) == null ? "" : customers.GetString(4).ToString();
                    newCustomer.Address = customers.GetValue(5) == null ? "" : customers.GetString(5).ToString();
                    newCustomer.City = customers.GetValue(6) == null ? "" : customers.GetString(6).ToString();
                    newCustomer.Region = customers.GetValue(7) == null ? "" : customers.GetString(7).ToString();
                    newCustomer.PostalCode = customers.GetValue(8) == null ? "" : customers.GetString(8).ToString();
                    newCustomer.Country = customers.GetValue(9) == null ? "" : customers.GetString(9).ToString();
                    newCustomer.Phone = customers.GetValue(10) == null ? "" : customers.GetString(10).ToString();
                    newCustomer.Fax = customers.GetValue(11) == null ? "" : customers.GetString(11).ToString();
                    _customers.Add(newCustomer);
                }
            }
        }

        private void LoadAllEmployees()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT ID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, " +
                                      "Address, City, Region, PostalCode, Country, HomePhone, Extension FROM Employees";

                IDataReader emps = command.ExecuteReader();
                while (emps.Read())
                {
                    Employee newEmp = new Employee();
                    newEmp.ID = emps.GetValue(0) == null ? "" : emps.GetInt32(0).ToString();
                    newEmp.LastName = emps.GetValue(1) == null ? "" : emps.GetString(1).ToString();
                    newEmp.FirstName = emps.GetValue(2) == null ? "" : emps.GetString(2).ToString();
                    newEmp.Title = emps.GetValue(3) == null ? "" : emps.GetString(3).ToString();
                    newEmp.TitleOfCourtesy = emps.GetValue(4) == null ? "" : emps.GetString(4).ToString();
                    newEmp.BirthDate = emps.GetValue(5) == null ? default(DateTime) : emps.GetDateTime(5);
                    newEmp.HireDate = emps.GetValue(6) == null ? default(DateTime) : emps.GetDateTime(6);
                    newEmp.Address = emps.GetValue(7) == null ? "" : emps.GetString(7).ToString();
                    newEmp.City = emps.GetValue(8) == null ? "" : emps.GetString(8).ToString();
                    newEmp.Region = emps.GetValue(9) == null ? "" : emps.GetString(9).ToString();
                    newEmp.PostalCode = emps.GetValue(10) == null ? "" : emps.GetString(10).ToString();
                    newEmp.Country = emps.GetValue(11) == null ? "" : emps.GetString(11).ToString();
                    newEmp.HomePhone = emps.GetValue(12) == null ? "" : emps.GetString(12).ToString();
                    _employees.Add(newEmp);
                }
            }
        }

        private void LoadAllShippers()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT ID, CompanyName, Phone FROM Shippers";

                IDataReader shippers = command.ExecuteReader();
                while (shippers.Read())
                {
                    Shipper newShippper = new Shipper();
                    newShippper.ID = shippers.GetValue(0) == null ? 0 : shippers.GetInt32(0);
                    newShippper.CompanyName = shippers.GetValue(1) == null ? "" : shippers.GetString(1).ToString();
                    newShippper.Phone = shippers.GetValue(2) == null ? "" : shippers.GetString(2).ToString();
                    _shippers.Add(newShippper);
                }
            }
        }

        private void LoadOrders()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                DbDataAdapter adapter = Factory.CreateDataAdapter();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT ID, OrderDate, RequiredDate, ShippedDate, ShipperID, Freight, " + 
                                        "ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, Shipcountry " + 
                                        "FROM Orders";

                adapter.SelectCommand = command;

                DataSet dataset = new DataSet();
                dataset.Tables.Add("Orders");
                adapter.Fill(dataset.Tables["Orders"]);

                this.orderBindingSource.DataSource = dataset.Tables["Orders"];
            }
        }
        public OrdersUserControl()
        {
            InitializeComponent();
        }

        public override string FormCaption { get { return Settings.Resources.OrdersFormCaption; } }
        public override dynamic GetEntitySet(ObjectContext context)
        {
            return new Model.Order();
        }

        public override BindingSource UserControlBindingSource { get { return orderBindingSource; } }

        private void LoadACustomers(string customerId)
        {
            if (string.IsNullOrEmpty(customerId))
                return;

            IList<Customer> custs = _customers.Where(x => x.ID == customerId).ToList();
            if(custs.Count > 0)
                this.billToComboBox.Text = custs.FirstOrDefault().Code;
        }

        private void LoadShippers()
        {
            if (string.IsNullOrEmpty(CurrentShipperID))
                return;

            IList<Shipper> shiprs = _shippers.Where(x => x.ID == Convert.ToInt32(CurrentShipperID)).ToList();
            if(shiprs.Count > 0)
                this.shipViaComboBox.Text = shiprs.FirstOrDefault().ID.ToString();
        }

        internal override void SetBindingSource()
        {
            base.SetBindingSource();

            //setOrderDetailsDataSource();
            var dataSet = new Order();//.FirstOrDefault();
            if (dataSet == null)
            {
                customerOrdersProductsDataGridView.DataSource = new Order();//.FirstOrDefault();
            }
            else
            {
                setOrderDetailsDataSource(dataSet.ID);  
            }
        }

        private void LoadTheEmployee()
        {
            if (string.IsNullOrEmpty(CurrentEmployeeID))
                return;

            this.salesPersonComboBox.Text = _employees.Where(x => x.ID == CurrentEmployeeID).ToList().FirstOrDefault().ID;
        }
        private string setOrderDetailsDataSource(string orderIdStr)
        {
            string customerToReturn = "";
            int orderId = -1;

            // read through text box.
            Int32.TryParse(this.iDTextBox.Text.ToString(), out orderId);

            CurrentOrderID = orderId.ToString();

            string query = "SELECT CustomerID, EmployeeID, ShipperID, Freight, OrderDetails FROM Orders WHERE ID = " + orderId + "";

            using (DbConnection connection = Factory.CreateConnection())
            {
                double totalExtendedPrice = 0.0;
                double freight = 0.0;
                connection.ConnectionString = ConnectionString;
                connection.Open();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = query;

                DataSet set = new DataSet();
                set.Tables.Add("OrderDetails");
                set.Tables["OrderDetails"].Columns.Add("ProductName", typeof(string));
                set.Tables["OrderDetails"].Columns.Add("UnitPrice", typeof(double));
                set.Tables["OrderDetails"].Columns.Add("Quantity", typeof(long));
                set.Tables["OrderDetails"].Columns.Add("Discount", typeof(double));
                set.Tables["OrderDetails"].Columns.Add("ExtendedPrice", typeof(double));
                IDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    freight = reader.GetDouble(3);
                    ArrayList orderDetails = new ArrayList((object[])reader.GetValue(4));

                    IEnumerator ie = orderDetails.GetEnumerator();
                    while (ie.MoveNext())
                    {
                        JSONDocument document = (JSONDocument)ie.Current;

                        string productName = "";
                        using (DbConnection innerConnection = Factory.CreateConnection())
                        {
                            innerConnection.ConnectionString = ConnectionString;
                            innerConnection.Open();
                            DbCommand innerCommand = Factory.CreateCommand();
                            innerCommand.Connection = innerConnection;
                            innerCommand.CommandText = "SELECT Name from Products WHERE ID = " + document["ProductID"] + "";

                            productName = (string)innerCommand.ExecuteScalar();
                        }
                        double extendedprice = ((double)document["UnitPrice"] * (long)document["Quantity"] * (1 - (double)document["Discount"]));
                        totalExtendedPrice += extendedprice;
                        set.Tables["OrderDetails"].Rows.Add(productName, document["UnitPrice"], document["Quantity"], document["Discount"], extendedprice);
                    }
                    customerToReturn = reader["CustomerID"].ToString();
                    CurrentEmployeeID = reader["EmployeeID"].ToString();
                    CurrentShipperID = reader["ShipperID"].ToString();
                }
                orderDetailBindingSource.DataSource = set;
                this.totalExtendedPriceTextBox.Text = totalExtendedPrice.ToString("C2");
                this.orderTotalTextBox.Text = (totalExtendedPrice + freight).ToString("C2");
            }
            return customerToReturn;
        }

        private void orderBindingSource_CurrentChanged(object sender, EventArgs e)
        {
            string customerId = setOrderDetailsDataSource(txtOrderId.Text);
            setBillToValues(customerId);

            LoadACustomers(customerId);
            LoadShippers();
            LoadTheEmployee();
        }

        private void billToComboBox_SelectedValueChanged(object sender, EventArgs e)
        {
            setBillToValues(txtOrderId.Text);
        }


        private void setBillToValues(string customerId)
        {
            if (string.IsNullOrEmpty(customerId))
                return;

            Customer cs = _customers.Where(x => x.ID == customerId).ToList().FirstOrDefault();
            billToAddressTextBox.Text = cs.Address;
            billToRegionTextBox.Text = cs.Region;
            billToCityTextBox.Text = cs.City;
            billToPostalCodeTextBox.Text = cs.PostalCode;
            billToCountryTextBox.Text = cs.Country;
        }
        
        //private InvoiceReport _report;
        private void printInvoiceButton_Click(object sender, EventArgs e)
        {
            //_report = new InvoiceReport();
            //_report.PreviewInvoice(Int32.Parse(iDTextBox.Text));
            todo();
        }

    }
}
